/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.*;
import java.text.*;
import java.util.Date;

/**
 *
 * @author user
 */
public class DatabaseConnection {

    private static DatabaseConnection instance = null;
    private static Connection connection = null;
    private String serverName = "localhost:3306";
    private String databaseName = "CarRedesign";
    private String databaseUsername = "root";// change the Username here
    private String databasePassword = "hansonlq";// change the password here
    private String driver = "com.mysql.jdbc.Driver";

    public DatabaseConnection() {
    }

    public static DatabaseConnection getInstance() {
        if (instance == null) {
            instance = new DatabaseConnection();
        }
        return instance;
    }

    public Connection getConnection() {
        createConnection();
        return connection;
    }

    public void createConnection() {
        if (connection == null) {
            try {
                Class.forName(driver);
                String url = "jdbc:mysql://" + serverName + "/" + databaseName + "?user=" + databaseUsername + "&password=" + databasePassword;
                connection = DriverManager.getConnection(url);

            } catch (ClassNotFoundException cnfe) {
                System.out.println("error:cnfe");
            } catch (SQLException sqle) {
                System.out.println("createConnection:error:sqle");
            } catch (Exception e) {
                System.out.println("createConnection:error:e");
            }
        }
    }

    public void disconnect() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        }
    }

    /** Handle car brand operation*/
    public ResultSet getCarBrandIcon() {
        ResultSet rs = null;
        try {
            String query = "select * from car_brand;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getCarBrandIconSize() {
        ResultSet rs = null;
        try {
            String query = "select count(*) from car_brand;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getCarBrandIconByPage(int from, int num) {
        ResultSet rs = null;
        try {
            String query = "select * from car_brand order by Car_Brand_ID limit " + from + "," + num + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getOneCarBrandIcon(String inName) {
        ResultSet rs = null;
        try {
            String query = "select * from car_brand where Car_Brand_Name = '" + inName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    public ResultSet getOneCarBrand(int inBrandID) {
        ResultSet rs = null;
        try {
            String query = "select * from car_brand where Car_Brand_ID = '" + inBrandID + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    /** Handle car brand series operation*/
    public ResultSet getBrandSeriesSize(int inBrandID) {
        ResultSet rs = null;
        try {
            String query = "select count(*) from brand_series where Car_Brand_ID = " + inBrandID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getBrandSeriesByPage(int inBrandID, int from, int num) {
        ResultSet rs = null;
        try {
            String query = "select * from brand_series where Car_Brand_ID = " + inBrandID + " order by Brand_Series_ID limit " + from + "," + num + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getOneBrandSeries(int inSeriesID) {
        ResultSet rs = null;
        try {
            String query = "select * from brand_series where Brand_Series_ID = '" + inSeriesID + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    /** Handle car parameter operation*/
    public ResultSet getCarParameterSize(int inBrandID, int inSeriesID) {
        ResultSet rs = null;
        try {
            String query = "select count(*) from car_parameter where Car_Brand_ID = " + inBrandID + " AND Brand_Series_ID = " + inSeriesID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqlegetCarParameterSize");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getCarParameterByPage(int inBrandID, int inSeriesID, int from, int num) {
        ResultSet rs = null;
        try {
            String query = "select * from car_parameter where Car_Brand_ID = " + inBrandID + " AND Brand_Series_ID = " + inSeriesID + " order by Car_ID limit " + from + "," + num + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqlegetCarParameterByPage");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    /** Handle user car operation*/
    public ResultSet getUserCarInformation(String inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from user_car_information where User_Car_Information_UserName = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    /** Handle car fitting brand operation*/
    public ResultSet getFittingBrandSize() {
        ResultSet rs = null;
        try {
            String query = "select count(*) from fitting_brand;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getFittingBrandSize(String inType) {
        ResultSet rs = null;
        try {
            String query = "select count(*) from fitting_brand where " + inType + " = true;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle:444");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getFittingBrandIconByPage(String inType, int from, int num) {
        ResultSet rs = null;
        try {
            String query = "select * from fitting_brand where " + inType + " = true order by Fitting_Brand_ID limit " + from + "," + num + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle:333");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    /** Handle fittings operation*/
    public ResultSet getFittingsSize(String inType, int inID) {
        ResultSet rs = null;
        try {
            String query = "select count(*) from " + inType + " where " + inType + " = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle:111");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    public ResultSet getFittingsByPage(String inType, int inID, int from, int num) {
        ResultSet rs = null;
        try {
            String query = "select * from " + inType + " where " + inType + " = " + inID + " order by " + inType + "_ID limit " + from + "," + num + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle:2222");
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:e");
        }
        return rs;
    }

    /** Handle user operation*/
    public boolean checkUserName(String inName) {
        ResultSet rs = null;
        try {
            String query = "select * from users where Users_UserName = '" + inName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean checkUser(String inName, String inPassword) {
        ResultSet rs = null;
        try {
            String query = "select * from users where Users_UserName = '" + inName + "' AND Users_Password = password('" + inPassword + "');";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public ResultSet getUserInformation(String inName, String inPassword) {
        ResultSet rs = null;
        try {
            String query = "select * from users where Users_UserName = '" + inName + "' AND Users_Password = password('" + inPassword + "');";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    public ResultSet getUserInformation(String inName) {
        ResultSet rs = null;
        try {
            String query = "select * from users where Users_UserName = '" + inName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    //don`t use it too much  It will show the sql to client
    public ResultSet executeQuery(String inQuery) {
        ResultSet rs = null;
        try {
            String query = inQuery;
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
        }
        return rs;
    }

    public ResultSet getUserCarInformationParameterID(String inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from User_Car_Information where User_Car_Information_UserName = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getWeightInKg:error:e");
        }
        return rs;
    }

    public ResultSet getUserCarInformationParameter(String UserCarInformationParameterID) {
        ResultSet rs = null;
        try {
            //b = Integer.parseInt(unladenWeightEU);
            int Sum = 0;
            Sum = Integer.parseInt(UserCarInformationParameterID);
            String query = "select * from User_Car_Information_Parameter where User_Car_Information_Parameter_ID = " + Sum + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getWeightInKg:error:e");
        }
        return rs;
    }

    public ResultSet getCarID(String inBrandID, String inSeriesID, String carID) {
        ResultSet rs = null;
        try {
            String query = "select * from Car_Parameter where Car_ID = '" + carID + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getWeightInKg:error:e");
        }
        return rs;
    }

    public ResultSet getWeightInKg(int inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from Weight_in_kg where Weight_in_kg_ID = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getWeightInKg:error:e");
        }
        return rs;
    }

    public ResultSet getWheels(int inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from Wheels where Wheels_ID = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getWheels:error:e");
        }
        return rs;
    }

    public ResultSet getPerformance(int inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from Performance where Performance_ID = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getPerformance:error:e");
        }
        return rs;
    }

    public ResultSet getEngine(int inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from Engine where Engine_ID = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getEngine:error:e");
        }
        return rs;
    }

    public ResultSet getFuelConsumption(int inUserName) {
        ResultSet rs = null;
        try {
            String query = "select * from Fuel_consumption where Fuel_consumption_ID = '" + inUserName + "';";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    //here for news display
    public ResultSet getNews(String inType, int inSize) {
        ResultSet rs = null;
        try {
            String query = "select * from news_db where type = '" + inType + "' order by Date_Time limit 0," + inSize + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getNews() {
        ResultSet rs = null;
        try {
            String query = "select * from news_db order by Date_Time limit 0,10;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getNews(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from news_db where ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    //use to register
    public void register(String inUsername, String inPassword, String inSex, String inMail, String inAddress) {
        try {
            String query = "INSERT INTO Users VALUES ('" + inUsername + "',password('" + inPassword + "'),'" + inSex + "','" + inAddress + "','" + inMail + "',0,null,'hansonl.jpg');";
            Statement stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    //for data save
    public boolean updata(String userName, String carModel, int Intake_ID, int Suspention_ID, int Mufeler_ID, int Hood_ID, int Charger_ID, int Front_Bumper_ID, int Rear_Bumper_ID, int Side_Skirt_ID, int Lower_Arm_Bar_ID, int Sopiler_ID, int Rim_ID) {
        ResultSet rs = null;
        try {
            String query = "select * from User_Car_Information where User_Car_Information_UserName = '" + userName + "';";
            System.out.println(query);
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            rs.next();
            int UCIP_ID = Integer.parseInt(rs.getString("User_Car_Information_Parameter_ID"));

            query = "update User_Car_Information set User_Car_Information_Parameter_Car_Model = '" + carModel + "',User_Car_Information_Parameter_Intake_ID = " + Intake_ID + ", User_Car_Information_Parameter_Suspention_ID" + Suspention_ID + ", User_Car_Information_Parameter_Mufeler_ID = " + Mufeler_ID + ", User_Car_Information_Parameter_Hood_ID = " + Hood_ID + ",User_Car_Information_Parameter_Charger_ID = " + Charger_ID + ",User_Car_Information_Parameter_Front_Bumper_ID = " + Front_Bumper_ID + ",User_Car_Information_Parameter_Rear_Bumper_ID = " + Rear_Bumper_ID + ", User_Car_Information_Parameter_Side_Skirt_ID= " + Side_Skirt_ID + ",User_Car_Information_Parameter_Lower_Arm_Bar_ID = " + Lower_Arm_Bar_ID + " , User_Car_Information_Parameter_Sopiler_ID = " + Sopiler_ID + ", User_Car_Information_Parameter_Rim_ID = " + Rim_ID + " where User_Car_Information_Parameter_ID = " + UCIP_ID + ";";
            System.out.println(query);
            //stmt = connection.createStatement();
            //rs = stmt.executeQuery(query);

            return true;

        } catch (SQLException sqle) {
            System.out.println("error:sqle");
            return false;
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
            return false;
        }
    }

    public boolean save(String userName, String carModel, int Intake_ID, int Suspention_ID, int Muflere_ID, int Hood_ID, int Charger_ID, int Front_Bumper_ID, int Rear_Bumper_ID, int Side_Skirt_ID, int Lower_Arm_Bar_ID, int Sopiler_ID, int Rim_ID) {
        ResultSet rs = null;
        int sequence = 1;
        int UCIPI = 0;
        try {
            String query = "select count(*) from User_Car_Information_Parameter;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }

            if (!carModel.equals("history")) {

                query = "insert into User_Car_Information_Parameter values (" + sequence + ",'" + carModel + "'," + Intake_ID + "," + Suspention_ID + "," + Muflere_ID + "," + Hood_ID + "," + Charger_ID + "," + Front_Bumper_ID + "," + Rear_Bumper_ID + "," + Side_Skirt_ID + "," + Lower_Arm_Bar_ID + "," + Sopiler_ID + "," + Rim_ID + ");";
                System.out.println(query);
                stmt = connection.createStatement();
                stmt.executeUpdate(query);

                query = "update User_Car_Information set User_Car_Information_Parameter_ID = " + sequence + " where User_Car_Information_UserName = '" + userName + "';";

                stmt = connection.createStatement();
                int effect = stmt.executeUpdate(query);
                System.out.println(query);

                if (effect == 0) {

                    int sequence2 = 1;
                    query = "select count(*) from User_Car_Information;";
                    stmt = connection.createStatement();
                    rs = stmt.executeQuery(query);
                    if (rs.next()) {
                        sequence2 = rs.getInt(1) + 1;
                    }
                    System.out.println(query);
                    query = "select * from car_parameter;";
                    stmt = connection.createStatement();
                    rs = stmt.executeQuery(query);
                    System.out.println(query);
                    String car_picture = null;

                    if (rs.next()) {
                        car_picture = rs.getString("Car_Picture");
                        System.out.println(car_picture);
                    //add brand
                    //'wait',1,nul  1 is related to evaluation
                    }

                    query = "INSERT INTO User_Car_Information VALUES (" + sequence2 + ",'" + userName + "','" + car_picture + "','wait',1,1,'ok',0,'bmw'," + sequence + ");";
                    System.out.println(query);
                    stmt = connection.createStatement();
                    stmt.executeUpdate(query);
                    System.out.println(query);
                }
            } else {
                query = "select * from User_Car_Information where User_Car_Information_UserName = '" + userName + "';";
                stmt = connection.createStatement();
                rs = stmt.executeQuery(query);
                System.out.println(query);

                if (rs.next()) {
                    UCIPI = Integer.parseInt(rs.getString("User_Car_Information_Parameter_ID"));
                }

                query = "update User_Car_Information_Parameter set User_Car_Information_Parameter_Intake_ID = " + Intake_ID + ",User_Car_Information_Parameter_Suspention_ID = " + Suspention_ID + ",User_Car_Information_Parameter_Mufeler_ID = " + Muflere_ID + ",User_Car_Information_Parameter_Hood_ID = " + Hood_ID + ",User_Car_Information_Parameter_Charger_ID = " + Charger_ID + ",User_Car_Information_Parameter_Front_Bumper_ID = " + Front_Bumper_ID + ",User_Car_Information_Parameter_Rear_Bumper_ID = " + Rear_Bumper_ID + ",User_Car_Information_Parameter_Side_Skirt_ID = " + Side_Skirt_ID + ",User_Car_Information_Parameter_Lower_Arm_Bar_ID = " + Lower_Arm_Bar_ID + ",User_Car_Information_Parameter_Sopiler_ID = " + Sopiler_ID + ",User_Car_Information_Parameter_Rim_ID = " + Rim_ID + " where User_Car_Information_Parameter_ID = '" + UCIPI + "';";

                stmt = connection.createStatement();
                int OK = stmt.executeUpdate(query);
                System.out.println(query);

                if (OK != 0) {
                    return true;
                } else {
                    return false;
                }
            }


            return true;
        } catch (SQLException sqle) {
            System.out.println("error:sqle:23123");
            return false;
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
            return false;
        }
    }

    //for fittings
    public ResultSet getRim(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from rim where Rim_ID = " + inID + ";";

            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getIntake(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from intake where Intake_ID = " + inID + ";";

            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getSuspention(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from suspention where Suspention_ID = " + inID + ";";

            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getLowerArmBar(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from lower_arm_bar where Lower_Arm_Bar_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getCharger(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from charger where Charger_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getSopiler(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from sopiler where Sopiler_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getFrontBumper(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from front_bumper where Front_Bumper_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getRearBumper(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from rear_bumper where Rear_Bumper_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getHood(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from hood where Hood_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getSideSkirt(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from side_skirt where Side_Skirt_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getMufeler(int inID) {
        ResultSet rs = null;
        try {
            String query = "select * from Mufeler where Mufeler_ID = " + inID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public boolean checkAdministatorName(String inName, String inType) {
        ResultSet rs = null;
        try {
            String query = null;
            if (inType.equals("admin")) {
                query = "select * from administrator where Administrator_Name = '" + inName + "';";
            } else {
                query = "select * from manufacturer_information where Manufacturer_Information_Name = '" + inName + "';";
            }
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean checkAdminister(String inName, String inPassword, String inType) {
        ResultSet rs = null;
        try {
            String query = null;
            if (inType.equals("admin")) {
                query = "select * from administrator where Administrator_Name = '" + inName + "' AND Administrator_PassWord = '" + inPassword + "';";
            } else {
                query = "select * from manufacturer_information where Manufacturer_Information_Name = '" + inName + "' AND Manufacturer_Information_PassWord = '" + inPassword + "';";
            }
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }
    //Manufacture to register

    public void Manufacture_Information(String inInformation_ID, String inInformation_Name, String inInformation_PassWord, String inInformation_Kind, String inInformation_Manager, String inInformation_Company_Web_Address, String inInformation_Email, String inInformation_Telephone, String inInformation_Fax, String inInformation_Location) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from User_Car_Information_Parameter;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO Manufacture_Information VALUES ('" + sequence + "','" + inInformation_Name + "','" + inInformation_PassWord + "','" + inInformation_Kind + "','" + inInformation_Manager + "','" + inInformation_Company_Web_Address + "','" + inInformation_Email + "','" + inInformation_Telephone + "','" + inInformation_Fax + "','" + inInformation_Location + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public ResultSet getManu() {
        ResultSet rs = null;
        try {
            String query = "select * from Manufacturer_Information;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public void insertNews(String inAddress, String inTitle, String inType, int inSequence) {
        ResultSet rs = null;
        SimpleDateFormat fateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();

        try {
            Statement stmt = connection.createStatement();
            String query = "INSERT INTO news_db VALUES ('" + inSequence + "','" + inAddress + "','" + inTitle + "','" + inTitle + "','" + fateFormat.format(date) + "','" + inType + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public void insertNews(String inTitle, int inID) {
        ResultSet rs = null;
        SimpleDateFormat fateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();

        try {
            Statement stmt = connection.createStatement();
            String query = "update news_db set NewsFullName = '" + inTitle + "', Abbreviation = '" + inTitle + "' ,Date_Time = '" + fateFormat.format(date) + "' where ID = " + inID + ";";
            System.out.println(query);
        stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public boolean DeleteTheNewsDBInformation(int ID) {
        int rs = 0;
        try {
            String query = null;
            query = "DELETE FROM news_db WHERE ID = " + ID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeUpdate(query);
            if (rs == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean CreatTheNewMF(String Name, String PassWord, String Kind, String Manager, String Company_Web_Address, String Email, String Telephone, String Fax, String Location) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from Manufacturer_Information;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }

            int sr = 0;
            query = null;
            query = "INSERT INTO Manufacturer_Information VALUES (" + sequence + ",'" + Name + "','" + PassWord + "','" + Kind + "','" + Manager + "','" + Company_Web_Address + "','" + Email + "','" + Telephone + "','" + Fax + "','" + Location + "');";
            stmt = connection.createStatement();
            sr = stmt.executeUpdate(query);
            if (sr == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean DeleteMF(int ID) {
        int rs = 0;
        try {
            String query = null;
            query = "DELETE FROM Manufacturer_Information WHERE Manufacturer_Information_ID = " + ID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeUpdate(query);
            if (rs == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public void Rim(String inRim_ID, String inRim, String inRim_PIC, String inPassenger_P_Metric_Tire, String inTire_Width, String inAspect_Ratio, String inConstruction_Code, String inRadial_Ply_Construction, String inRim_Diameter) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from Rim;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO Rim VALUES ('" + sequence + "','" + inRim_ID + "','" + inRim + "','" + inRim_PIC + "','" + inPassenger_P_Metric_Tire + "','" + inTire_Width + "','" + inAspect_Ratio + "','" + inConstruction_Code + "','" + inRadial_Ply_Construction + "','" + inRim_Diameter + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public ResultSet getRim() {
        ResultSet rs = null;
        try {
            String query = "select * from Rim;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public ResultSet getAudit_Plan() {
        ResultSet rs = null;
        try {
            String query = "select * from Rim;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public void Audit_Plan(String inPlan_ID, String inPlan_UserName, String inPlan_Data) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from Audit_Plan;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO Rim VALUES ('" + sequence + "','" + inPlan_ID + "','" + inPlan_UserName + "','" + inPlan_Data + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public ResultSet getUser_Car_Information() {
        ResultSet rs = null;
        try {
            String query = "select * from User_Car_Information;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public void User_Car_Information(String inUser_Car_Information_ID, String inUser_Car_Information_UserName, String inUser_Car_Information_Parameter) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from User_Car_Information;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO User_Car_Information VALUES ('" + sequence + "','" + inUser_Car_Information_ID + "','" + inUser_Car_Information_UserName + "','" + inUser_Car_Information_Parameter + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public ResultSet getAdvertising() {
        ResultSet rs = null;
        try {
            String query = "select * from Advertising;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public void Advertising(String inAdvertising_ID, String inAdvertising_Name, String inAdvertising_Picture_ID, String inAdvertising_Date) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from Advertising;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO Advertising ('" + sequence + "','" + inAdvertising_ID + "','" + inAdvertising_Name + "','" + inAdvertising_Picture_ID + "', '" + inAdvertising_Date + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }

    public boolean AddAD(String inUserName, String Picture_ID, String AD_Date) {
        ResultSet rs = null;
        int AD = 0;
        int sequence = 0;
        try {
            String query = "select count(*) from Advertising;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = null;
            query = "INSERT INTO Advertising values (" + sequence + ",'" + inUserName + "','" + Picture_ID+"','" + AD_Date +"');";
            stmt = connection.createStatement();
            AD = stmt.executeUpdate(query);
            if (AD == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean DeleteAD(int ID) {
        int rs = 0;
        try {
            String query = null;
            query = "DELETE FROM Advertising WHERE Advertising_ID = " + ID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeUpdate(query);
            if (rs == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean AddDate(String theRim, String Metric_Tire, String Tire_Width ,String Aspect_Ratio, String Rim_Diameter) {
        ResultSet rs = null;
        String aa = null;
        int AD = 0;
        int sequence = 0;
        int the_Rim = Integer.parseInt(theRim);
        try {
            String query = "select count(*) from Rim;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = null;
            query = "INSERT INTO Rim values (" + sequence + "," + the_Rim + ",'" + aa + "','" + Metric_Tire + "','" + Tire_Width + "','" + Aspect_Ratio + "','" + aa + "','" + aa + "','" + Rim_Diameter + "');";
            stmt = connection.createStatement();
            AD = stmt.executeUpdate(query);
            if (AD == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

    public boolean DeleteDate(int ID) {
        int rs = 0;
        try {
            String query = null;
            query = "DELETE FROM Rim WHERE Rim_ID = " + ID + ";";
            Statement stmt = connection.createStatement();
            rs = stmt.executeUpdate(query);
            if (rs == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            System.out.println("error:sqle" + sqle);
            return false;
        } catch (Exception e) {
            System.out.println("getCarBrandIcon:error:" + e);
            return false;
        }
    }

        public ResultSet getUser_Car_Information_Parameter() {
        ResultSet rs = null;
        try {
            String query = "select * from User_Car_Information_Parameter;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
        return rs;
    }

    public void User_Car_Information_Parameter(String inUser_Car_Information_Parameter_ID, String inUser_Car_Information_Parameter_Intake_ID, String inUser_Car_Information_Parameter_Rim_ID, String inUser_Car_Information_Parameter_Charger_ID,String inUser_Car_Information_Parameter_Car_Model) {
        ResultSet rs = null;
        int sequence = 1;
        try {
            String query = "select count(*) from User_Car_Information_Parameter;";
            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                sequence = rs.getInt(1) + 1;
            }
            query = "INSERT INTO User_Car_Information_Parameter ('" + sequence + "','" + inUser_Car_Information_Parameter_ID + "','" + inUser_Car_Information_Parameter_Intake_ID + "','" + inUser_Car_Information_Parameter_Rim_ID + "', '" + inUser_Car_Information_Parameter_Charger_ID + "', '" + inUser_Car_Information_Parameter_Car_Model + "');";
            stmt = connection.createStatement();
            stmt.executeUpdate(query);
        } catch (SQLException sqle) {
            System.out.println("error:sqle");
        } catch (Exception e) {
            System.out.println("getFuelConsumption:error:e");
        }
    }
}
